package com.suncaper.han_yi_system.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.suncaper.han_yi_system.mapper.ApplicationMapper;
import com.suncaper.han_yi_system.mapper.BatchMapper;
import com.suncaper.han_yi_system.mapper.StudentMapper;
import com.suncaper.han_yi_system.mapper.UserMapper;
import com.suncaper.han_yi_system.pojo.dto.ApplicationQueryDto;
import com.suncaper.han_yi_system.pojo.dto.ApplicationResultDto;
import com.suncaper.han_yi_system.pojo.entity.*;
import com.suncaper.han_yi_system.pojo.vo.*;
import com.suncaper.han_yi_system.service.ApplicationService;
import com.suncaper.han_yi_system.service.ClothingStyleService;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

@Service
public class ApplicationServiceImpl extends ServiceImpl<ApplicationMapper, Application> implements ApplicationService {

    @Autowired
    UserMapper userMapper;

    @Autowired
    StudentMapper studentMapper;

    @Autowired
    BatchMapper batchMapper;

    @Autowired
    private ClothingStyleService clothingStyleService;


    @Override
    public int saveApplication(Application application) {
        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        LoginUser loginUser = (LoginUser)authentication.getPrincipal();
        Long userId = loginUser.getUser().getId();

        Long studentId = studentMapper.selectOne(new QueryWrapper<Student>().eq("account", userId)).getId();
        QueryWrapper<Application> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("student_id", studentId);
        queryWrapper.eq("batch_id", application.getBatchId());
        if (getBaseMapper().selectCount(queryWrapper) > 0) return 0;

        application.setStudentId(studentId);
        application.setApplyTime(LocalDateTime.now());
        application.setStatus((byte) 1);
        application.setCollegeResult((byte) 1);
        application.setSchoolResult((byte) 1);
        return getBaseMapper().insert(application);
    }

    @Override
    public Page<ApplicationVo> findApplicationByQueryDto(ApplicationQueryDto queryDto) {
        Page<ApplicationVo> page = new Page<>(queryDto.getPage(), queryDto.getLimit());
        return getBaseMapper().findApplicationByQueryDto(page, queryDto);
    }

    @Override
    public Page<ApplicationVo> findMyApplication(ApplicationQueryDto queryDto) {
        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        LoginUser loginUser = (LoginUser)authentication.getPrincipal();
        Long userId = loginUser.getUser().getId();
        List<Role> roles = userMapper.findRoleByUserId(userId);
        roles.forEach(role -> {
            if (role.getRoleCode().equals("ROLE_STUDENT")) {
                queryDto.setStudentId(studentMapper.selectOne(new QueryWrapper<Student>()
                        .eq("account", userId)).getId());
            }
            if (role.getRoleCode().equals("ROLE_COUNSELOR")) {
                queryDto.setCounselorId(userId);
            }
            if (role.getRoleCode().equals("ROLE_COLLEGE")) {
                queryDto.setCollegeId(userId);
            }
        });
        Page<ApplicationVo> page = new Page<>(queryDto.getPage(), queryDto.getLimit());
        return getBaseMapper().findMyApplication(page,queryDto);
    }

    @Override
    public int resultApplication(ApplicationResultDto resultDto) {
        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        LoginUser loginUser = (LoginUser)authentication.getPrincipal();
        Long userId = loginUser.getUser().getId();
        List<String> roles = userMapper.findRoleByUserId(userId).stream().map(Role::getRoleCode).toList();
        int n = 0;
        for (String role : roles) {
            n = switch (role) {
                case "ROLE_COUNSELOR" -> getBaseMapper().resultByCounselor(resultDto, userId);
                case "ROLE_COLLEGE" -> getBaseMapper().resultByCollege(resultDto, userId);
                case "ROLE_SCHOOL" -> getBaseMapper().resultBySchool(resultDto);
                default -> n;
            };
        }
        return n;
    }

    @Override
    @Transactional
    public void submitApplication(List<Long> ids) {
        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        LoginUser loginUser = (LoginUser)authentication.getPrincipal();
        Long userId = loginUser.getUser().getId();
        List<String> roles = userMapper.findRoleByUserId(userId).stream().map(Role::getRoleCode).toList();

        QueryWrapper<Application> applicationWrapper = new QueryWrapper<>();
        QueryWrapper<Student> studentWrapper = new QueryWrapper<>();

        for (String role : roles) {
            switch (role) {
                case "ROLE_COUNSELOR" :
                    getBaseMapper().submitByCounselor(userId,ids);
                case "ROLE_COLLEGE" :
                    getBaseMapper().submitByCollege(userId,ids);
                case "ROLE_SCHOOL" :
                    getBaseMapper().submitBySchool(ids);
            }
        }
    }

    @Override
    public ApplicationStatsVo getApplicationStats(ApplicationQueryDto applicationQueryDto) {

        ApplicationStatsVo applicationStatsVo = new ApplicationStatsVo();

        QueryWrapper<Application> queryWrapper = new QueryWrapper<>();

        if(!Objects.isNull(applicationQueryDto.getAcademicYear())){
            List<Batch> batches = batchMapper.selectList(new QueryWrapper<Batch>()
                    .eq("academic_year", applicationQueryDto.getAcademicYear()));
            queryWrapper.in("batch_id", batches.stream().map(Batch::getId).toList());
        }
        if (!Objects.isNull(applicationQueryDto.getCollegeId())){
            List<Student> students = studentMapper.selectList(new QueryWrapper<Student>()
                    .eq("college", applicationQueryDto.getCollegeId()));
            queryWrapper.in("student_id", students.stream().map(Student::getId).toList());
        }

        Long count = getBaseMapper().selectCount(queryWrapper);
        applicationStatsVo.setAllCount(count);

        if (count == 0) {
            applicationStatsVo.setPassedCount(0);
            applicationStatsVo.setRejectedCount(0);
            applicationStatsVo.setVerifyCount(0);
        }else {
            int passedCount = 0;
            int rejectedCount = 0;
            int verifyCount = 0;
            List<Application> applications = getBaseMapper().selectList(queryWrapper);

            for (Application application : applications) {
                switch (application.getStatus()) {
                    case 4: passedCount++; break;
                    case 0: rejectedCount++; break;
                    default: verifyCount++;
                }
            }
            applicationStatsVo.setPassedCount(passedCount);
            applicationStatsVo.setRejectedCount(rejectedCount);
            applicationStatsVo.setVerifyCount(verifyCount);
        }

        return applicationStatsVo;
    }

    @Override
    public XSSFWorkbook exportStatistics(ApplicationQueryDto applicationQueryDto) throws Exception {

        /*ApplicationStatsVo applicationStats = this.getApplicationStats(applicationQueryDto);

        Map<String, Object> clothingStats = clothingStyleService.getClothingStats(applicationQueryDto);

        ClothingStatsVo clothingStatsVo = (ClothingStatsVo) clothingStats.get("clothingStatsVo");

        List<StyleStatsVo> styleStatsVos = (List<StyleStatsVo>) clothingStats.get("styleStatsVos");

        // 2. 通过POI将数据写入到Excel文件中
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/统计数据导出模版.xlsx");

        try {
            // 基于模版文件创建一个新的Excel文件
            XSSFWorkbook excel = new XSSFWorkbook(in);

            // 获取表格文件的Sheet页
            XSSFSheet sheet = excel.getSheet("Sheet1");

            // 将统计数据填写到Excel中
            XSSFRow row1 = sheet.getRow(2);
            if (row1 != null) {
                row1.getCell(0).setCellValue(applicationStats.getAllCount()); // 总申请数
                row1.getCell(1).setCellValue(applicationStats.getPassedCount()); // 通过数
                row1.getCell(2).setCellValue(applicationStats.getRejectedCount()); // 驳回数
                row1.getCell(3).setCellValue(applicationStats.getVerifyCount()); // 待审核数
            }

            XSSFRow row2 = sheet.getRow(6);
            if (row2 != null && clothingStatsVo != null) {
                row2.getCell(0).setCellValue(clothingStatsVo.getAllCount()); // 总款式
                row2.getCell(1).setCellValue(clothingStatsVo.getManCount()); // 男款
                row2.getCell(2).setCellValue(clothingStatsVo.getWomanCount()); // 女款
                row2.getCell(3).setCellValue(clothingStatsVo.getGenderCount()); // 男女皆宜款
            }

            // 处理衣服款式明细 - 垂直合并居中
            if (styleStatsVos != null) {
                int currentRow = 10;

                for (int i = 0; i < styleStatsVos.size(); i++) {
                    StyleStatsVo styleStatsVo = styleStatsVos.get(i);
                    List<SizeVo> sizeVos = styleStatsVo.getSizeVos();

                    if (sizeVos != null && !sizeVos.isEmpty()) {
                        // 计算需要合并的行数
                        int mergeEndRow = currentRow + sizeVos.size() - 1;

                        // 填充每个尺码的数据
                        for (int j = 0; j < sizeVos.size(); j++) {
                            SizeVo sizeVo = sizeVos.get(j);
                            XSSFRow row = getOrCreateRow(sheet, currentRow + j);

                            // 第一行填充年份、性别、款式（需要合并）
                            XSSFCell yearCell = row.getCell(0);
                            if (yearCell == null) yearCell = row.createCell(0);
                            yearCell.setCellValue(styleStatsVo.getAcademicYear());

                            XSSFCell genderCell = row.getCell(1);
                            if (genderCell == null) genderCell = row.createCell(1);
                            genderCell.setCellValue(styleStatsVo.getGenderLimit());

                            XSSFCell styleCell = row.getCell(2);
                            if (styleCell == null) styleCell = row.createCell(2);
                            styleCell.setCellValue(styleStatsVo.getName());


                            // 填充尺码和数量（不需要合并）
                            XSSFCell sizeCell = row.getCell(3);
                            if (sizeCell == null) sizeCell = row.createCell(3);
                            sizeCell.setCellValue(sizeVo.getSize());

                            XSSFCell countCell = row.getCell(4);
                            if (countCell == null) countCell = row.createCell(4);
                            countCell.setCellValue(sizeVo.getCount());

                            // 填充合计（只在第一行显示）
                            XSSFCell totalCell = row.getCell(5);
                            if (totalCell == null) totalCell = row.createCell(5);
                            totalCell.setCellValue(styleStatsVo.getCount());
                        }

                        // 合并年份、性别、款式、合计列的单元格
                        if (sizeVos.size() > 1) {
                            mergeCellsVertically(sheet, 0, currentRow, mergeEndRow); // 年份列
                            mergeCellsVertically(sheet, 1, currentRow, mergeEndRow); // 性别列
                            mergeCellsVertically(sheet, 2, currentRow, mergeEndRow); // 款式列
                            mergeCellsVertically(sheet, 5, currentRow, mergeEndRow); // 合计列
                        }

                        // 设置垂直居中对齐
                        setVerticalCenterAlignment(sheet, currentRow, mergeEndRow);

                        // 更新当前行号
                        currentRow = mergeEndRow + 1;
                    } else {
                        // 如果没有尺码数据，仍然显示基本行
                        XSSFRow row = getOrCreateRow(sheet, currentRow);

                        XSSFCell yearCell = row.getCell(0);
                        if (yearCell == null) yearCell = row.createCell(0);
                        yearCell.setCellValue(styleStatsVo.getAcademicYear());

                        XSSFCell genderCell = row.getCell(1);
                        if (genderCell == null) genderCell = row.createCell(1);
                        genderCell.setCellValue(styleStatsVo.getGenderLimit());

                        XSSFCell styleCell = row.getCell(2);
                        if (styleCell == null) styleCell = row.createCell(2);
                        styleCell.setCellValue(styleStatsVo.getName());

                        // 尺码和数量留空或填默认值
                        XSSFCell sizeCell = row.getCell(3);
                        if (sizeCell == null) sizeCell = row.createCell(3);
                        sizeCell.setCellValue("");

                        XSSFCell countCell = row.getCell(4);
                        if (countCell == null) countCell = row.createCell(4);
                        countCell.setCellValue(0);

                        XSSFCell totalCell = row.getCell(5);
                        if (totalCell == null) totalCell = row.createCell(5);
                        totalCell.setCellValue(styleStatsVo.getCount());

                        currentRow++;
                    }
                }
            }*/
        ApplicationStatsVo applicationStats = this.getApplicationStats(applicationQueryDto);

        Map<String, Object> clothingStats = clothingStyleService.getClothingStats(applicationQueryDto);

        ClothingStatsVo clothingStatsVo = (ClothingStatsVo) clothingStats.get("clothingStatsVo");

        List<StyleStatsVo> styleStatsVos = (List<StyleStatsVo>) clothingStats.get("styleStatsVos");

        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/统计数据导出模版.xlsx");

        // 基于模版文件创建一个新的Excel文件
        XSSFWorkbook excel = new XSSFWorkbook(in);

        // 获取表格文件的Sheet页
        XSSFSheet sheet = excel.getSheet("Sheet1");

        // 将统计数据填写到Excel中
        XSSFRow row1 = sheet.getRow(2);
        if (row1 != null) {
            if (applicationStats.getAllCount() != null) {
                row1.createCell(0).setCellValue(applicationStats.getAllCount().doubleValue());
            }  else {
                row1.createCell(0).setCellValue(0); // 总申请数
            }
            if(applicationStats.getPassedCount() != null) {
                row1.createCell(1).setCellValue(applicationStats.getPassedCount().doubleValue());
            } else {
                row1.createCell(1).setCellValue(0); // 通过数
            }
            if(applicationStats.getRejectedCount() != null) {
                row1.createCell(2).setCellValue(applicationStats.getRejectedCount().doubleValue());
            } else {
                row1.createCell(2).setCellValue(0); // 驳回数
            }
            if (applicationStats.getVerifyCount() != null) {
                row1.createCell(3).setCellValue(applicationStats.getVerifyCount().doubleValue());
            } else {
                row1.createCell(3).setCellValue(0); // 待审核数
            }
        }

        XSSFRow row2 = sheet.getRow(6);
        if (row2 != null && clothingStatsVo != null) {
            if (clothingStatsVo.getAllCount() != null) {
                row2.createCell(0).setCellValue(clothingStatsVo.getAllCount().doubleValue());
            } else {
                row2.createCell(0).setCellValue(0); // 总款式
            }
            if (clothingStatsVo.getManCount() != null) {
                row2.createCell(1).setCellValue(clothingStatsVo.getManCount().doubleValue());
            } else {
                row2.createCell(1).setCellValue(0); // 男款
            }
            if (clothingStatsVo.getWomanCount() != null) {
                row2.createCell(2).setCellValue(clothingStatsVo.getWomanCount().doubleValue());
            } else {
                row2.createCell(2).setCellValue(0); // 女款
            }
            if (clothingStatsVo.getGenderCount() != null) {
                row2.createCell(3).setCellValue(clothingStatsVo.getGenderCount().doubleValue());
            } else {
                row2.createCell(3).setCellValue(0); // 男女皆宜款
            }
        }

        // 处理衣服款式明细 - 垂直合并居中
        if (styleStatsVos != null) {
            int currentRow = 10;

            for (int i = 0; i < styleStatsVos.size(); i++) {
                StyleStatsVo styleStatsVo = styleStatsVos.get(i);
                List<SizeVo> sizeVos = styleStatsVo.getSizeVos();

                if (sizeVos != null && !sizeVos.isEmpty()) {
                    // 计算需要合并的行数
                    int mergeEndRow = currentRow + sizeVos.size() - 1;

                    // 填充每个尺码的数据
                    for (int j = 0; j < sizeVos.size(); j++) {
                        SizeVo sizeVo = sizeVos.get(j);
                        XSSFRow row = getOrCreateRow(sheet, currentRow + j);

                        // 填充年份、性别、款式（需要合并）
                        XSSFCell yearCell = row.getCell(0);
                        if (yearCell == null) yearCell = row.createCell(0);
                        yearCell.setCellValue(styleStatsVo.getAcademicYear().toString());

                        XSSFCell genderCell = row.getCell(1);
                        if (genderCell == null) genderCell = row.createCell(1);
                        genderCell.setCellValue(styleStatsVo.getGenderLimit() == 0 ? "男" : "女");

                        XSSFCell styleCell = row.getCell(2);
                        if (styleCell == null) styleCell = row.createCell(2);
                        styleCell.setCellValue(styleStatsVo.getName());


                        // 填充尺码和数量（不需要合并）
                        XSSFCell sizeCell = row.getCell(3);
                        if (sizeCell == null) sizeCell = row.createCell(3);
                        sizeCell.setCellValue(sizeVo.getSize());

                        XSSFCell countCell = row.getCell(4);
                        if (countCell == null) countCell = row.createCell(4);
                        if (sizeVo.getCount() != null) {
                            countCell.setCellValue(sizeVo.getCount().doubleValue());
                        } else {
                            countCell.setCellValue(0);
                        }

                        // 填充合计
                        XSSFCell totalCell = row.getCell(5);
                        if (totalCell == null) totalCell = row.createCell(5);
                        if (styleStatsVo.getCount() != null) {
                            totalCell.setCellValue(styleStatsVo.getCount().doubleValue());
                        } else {
                            totalCell.setCellValue(0);
                        }
                    }

                    // 合并年份、性别、款式、合计列的单元格
                    if (sizeVos.size() > 1) {
                        mergeCellsVertically(sheet, 0, currentRow, mergeEndRow); // 年份列
                        mergeCellsVertically(sheet, 1, currentRow, mergeEndRow); // 性别列
                        mergeCellsVertically(sheet, 2, currentRow, mergeEndRow); // 款式列
                        mergeCellsVertically(sheet, 5, currentRow, mergeEndRow); // 合计列
                    }

                    // 设置垂直居中对齐
                    setVerticalCenterAlignment(sheet, currentRow, mergeEndRow);

                    // 更新当前行号
                    currentRow = mergeEndRow + 1;
                } else {
                    // 如果没有尺码数据，仍然显示基本行
                    XSSFRow row = getOrCreateRow(sheet, currentRow);

                    XSSFCell yearCell = row.getCell(0);
                    if (yearCell == null) yearCell = row.createCell(0);
                    yearCell.setCellValue(styleStatsVo.getAcademicYear());

                    XSSFCell genderCell = row.getCell(1);
                    if (genderCell == null) genderCell = row.createCell(1);
                    genderCell.setCellValue(styleStatsVo.getGenderLimit() == 0 ? "男" : "女");

                    XSSFCell styleCell = row.getCell(2);
                    if (styleCell == null) styleCell = row.createCell(2);
                    styleCell.setCellValue(styleStatsVo.getName());

                    // 尺码和数量留空或填默认值
                    XSSFCell sizeCell = row.getCell(3);
                    if (sizeCell == null) sizeCell = row.createCell(3);
                    sizeCell.setCellValue("");

                    XSSFCell countCell = row.getCell(4);
                    if (countCell == null) countCell = row.createCell(4);
                    countCell.setCellValue(0);

                    XSSFCell totalCell = row.getCell(5);
                    if (totalCell == null) totalCell = row.createCell(5);
                    if (styleStatsVo.getCount() != null) {
                        totalCell.setCellValue(styleStatsVo.getCount().doubleValue());
                    } else {
                        totalCell.setCellValue(0);
                    }

                    currentRow++;
                }
            }
        }

        return excel;
    }

    @Override
    public void exportStatisticsToFile(ApplicationQueryDto applicationQueryDto, HttpServletResponse response) {

        ApplicationStatsVo applicationStats = this.getApplicationStats(applicationQueryDto);

        Map<String, Object> clothingStats = clothingStyleService.getClothingStats(applicationQueryDto);

        ClothingStatsVo clothingStatsVo = (ClothingStatsVo) clothingStats.get("clothingStatsVo");

        List<StyleStatsVo> styleStatsVos = (List<StyleStatsVo>) clothingStats.get("styleStatsVos");

        // 2. 通过POI将数据写入到Excel文件中
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/统计数据导出模版.xlsx");

        try {
            // 基于模版文件创建一个新的Excel文件
            XSSFWorkbook excel = new XSSFWorkbook(in);

            // 获取表格文件的Sheet页
            XSSFSheet sheet = excel.getSheet("Sheet1");

            // 将统计数据填写到Excel中
            XSSFRow row1 = sheet.getRow(2);
            if (row1 != null) {
                if (applicationStats.getAllCount() != null) {
                    row1.createCell(0).setCellValue(applicationStats.getAllCount().doubleValue());
                }  else {
                    row1.createCell(0).setCellValue(0); // 总申请数
                }
                if(applicationStats.getPassedCount() != null) {
                    row1.createCell(1).setCellValue(applicationStats.getPassedCount().doubleValue());
                } else {
                    row1.createCell(1).setCellValue(0); // 通过数
                }
                if(applicationStats.getRejectedCount() != null) {
                    row1.createCell(2).setCellValue(applicationStats.getRejectedCount().doubleValue());
                } else {
                    row1.createCell(2).setCellValue(0); // 驳回数
                }
                if (applicationStats.getVerifyCount() != null) {
                    row1.createCell(3).setCellValue(applicationStats.getVerifyCount().doubleValue());
                } else {
                    row1.createCell(3).setCellValue(0); // 待审核数
                }
            }

            XSSFRow row2 = sheet.getRow(6);
            if (row2 != null && clothingStatsVo != null) {
                if (clothingStatsVo.getAllCount() != null) {
                    row2.createCell(0).setCellValue(clothingStatsVo.getAllCount().doubleValue());
                } else {
                    row2.createCell(0).setCellValue(0); // 总款式
                }
                if (clothingStatsVo.getManCount() != null) {
                    row2.createCell(1).setCellValue(clothingStatsVo.getManCount().doubleValue());
                } else {
                    row2.createCell(1).setCellValue(0); // 男款
                }
                if (clothingStatsVo.getWomanCount() != null) {
                    row2.createCell(2).setCellValue(clothingStatsVo.getWomanCount().doubleValue());
                } else {
                    row2.createCell(2).setCellValue(0); // 女款
                }
                if (clothingStatsVo.getGenderCount() != null) {
                    row2.createCell(3).setCellValue(clothingStatsVo.getGenderCount().doubleValue());
                } else {
                    row2.createCell(3).setCellValue(0); // 男女皆宜款
                }
            }

            // 处理衣服款式明细 - 垂直合并居中
            if (styleStatsVos != null) {
                int currentRow = 10;

                for (int i = 0; i < styleStatsVos.size(); i++) {
                    StyleStatsVo styleStatsVo = styleStatsVos.get(i);
                    List<SizeVo> sizeVos = styleStatsVo.getSizeVos();

                    if (sizeVos != null && !sizeVos.isEmpty()) {
                        // 计算需要合并的行数
                        int mergeEndRow = currentRow + sizeVos.size() - 1;

                        // 填充每个尺码的数据
                        for (int j = 0; j < sizeVos.size(); j++) {
                            SizeVo sizeVo = sizeVos.get(j);
                            XSSFRow row = getOrCreateRow(sheet, currentRow + j);

                            // 填充年份、性别、款式（需要合并）
                            XSSFCell yearCell = row.getCell(0);
                            if (yearCell == null) yearCell = row.createCell(0);
                            yearCell.setCellValue(styleStatsVo.getAcademicYear().toString());

                            XSSFCell genderCell = row.getCell(1);
                            if (genderCell == null) genderCell = row.createCell(1);
                            genderCell.setCellValue(styleStatsVo.getGenderLimit() == 0 ? "男" : "女");

                            XSSFCell styleCell = row.getCell(2);
                            if (styleCell == null) styleCell = row.createCell(2);
                            styleCell.setCellValue(styleStatsVo.getName());


                            // 填充尺码和数量（不需要合并）
                            XSSFCell sizeCell = row.getCell(3);
                            if (sizeCell == null) sizeCell = row.createCell(3);
                            sizeCell.setCellValue(sizeVo.getSize());

                            XSSFCell countCell = row.getCell(4);
                            if (countCell == null) countCell = row.createCell(4);
                            if (sizeVo.getCount() != null) {
                                countCell.setCellValue(sizeVo.getCount().doubleValue());
                            } else {
                                countCell.setCellValue(0);
                            }

                            // 填充合计
                            XSSFCell totalCell = row.getCell(5);
                            if (totalCell == null) totalCell = row.createCell(5);
                            if (styleStatsVo.getCount() != null) {
                                totalCell.setCellValue(styleStatsVo.getCount().doubleValue());
                            } else {
                                totalCell.setCellValue(0);
                            }
                        }

                        // 合并年份、性别、款式、合计列的单元格
                        if (sizeVos.size() > 1) {
                            mergeCellsVertically(sheet, 0, currentRow, mergeEndRow); // 年份列
                            mergeCellsVertically(sheet, 1, currentRow, mergeEndRow); // 性别列
                            mergeCellsVertically(sheet, 2, currentRow, mergeEndRow); // 款式列
                            mergeCellsVertically(sheet, 5, currentRow, mergeEndRow); // 合计列
                        }

                        // 设置垂直居中对齐
                        setVerticalCenterAlignment(sheet, currentRow, mergeEndRow);

                        // 更新当前行号
                        currentRow = mergeEndRow + 1;
                    } else {
                        // 如果没有尺码数据，仍然显示基本行
                        XSSFRow row = getOrCreateRow(sheet, currentRow);

                        XSSFCell yearCell = row.getCell(0);
                        if (yearCell == null) yearCell = row.createCell(0);
                        yearCell.setCellValue(styleStatsVo.getAcademicYear());

                        XSSFCell genderCell = row.getCell(1);
                        if (genderCell == null) genderCell = row.createCell(1);
                        genderCell.setCellValue(styleStatsVo.getGenderLimit() == 0 ? "男" : "女");

                        XSSFCell styleCell = row.getCell(2);
                        if (styleCell == null) styleCell = row.createCell(2);
                        styleCell.setCellValue(styleStatsVo.getName());

                        // 尺码和数量留空或填默认值
                        XSSFCell sizeCell = row.getCell(3);
                        if (sizeCell == null) sizeCell = row.createCell(3);
                        sizeCell.setCellValue("");

                        XSSFCell countCell = row.getCell(4);
                        if (countCell == null) countCell = row.createCell(4);
                        countCell.setCellValue(0);

                        XSSFCell totalCell = row.getCell(5);
                        if (totalCell == null) totalCell = row.createCell(5);
                        if (styleStatsVo.getCount() != null) {
                            totalCell.setCellValue(styleStatsVo.getCount().doubleValue());
                        } else {
                            totalCell.setCellValue(0);
                        }

                        currentRow++;
                    }
                }
            }

            String filePath = ".\\src\\main\\resources\\template\\test.xlsx";
            FileOutputStream fileOut = new FileOutputStream(filePath);
            excel.write(fileOut);
            fileOut.close();
            excel.close();

            System.out.println("文件已保存到: " + filePath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 辅助方法：获取或创建行
    private XSSFRow getOrCreateRow(XSSFSheet sheet, int rowNum) {
        XSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        return row;
    }

    // 辅助方法：垂直合并单元格
    private void mergeCellsVertically(XSSFSheet sheet, int column, int startRow, int endRow) {
        if (startRow < endRow) {
            sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, column, column));
        }
    }

    // 辅助方法：设置垂直居中对齐
    private void setVerticalCenterAlignment(XSSFSheet sheet, int startRow, int endRow) {
        // 创建居中样式
        XSSFCellStyle centerStyle = (XSSFCellStyle) sheet.getWorkbook().createCellStyle();
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 应用样式到指定行的单元格
        for (int i = startRow; i <= endRow; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < 6; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellStyle(centerStyle);
                    }
                }
            }
        }
    }
}
